<?php

namespace MLM\Model\Notification;

class Storage extends \MLM\Model\Generic\Db\Storage {


    public function getCountNewMsg($memberId){

        $sql = "select count(1) as num 
                from msg_inbox mi, member_contact mc, contact c
                where mc.member_id = ? and c.contact_id = mc.contact_id and mi.msisdn = c.contact_mobile";

        $stmt = $this->_db->query($sql);
        $res = $stmt->execute(array($memberId));

        return $res;
    }

    public function getNewMsgList($memberId){

        $sql = "select mi.*, m.member_name, img_profile 
                from msg_inbox mi, member_contact mc, contact c, members m
                where mc.member_id = ? and c.contact_id = mc.contact_id and mi.msisdn = c.contact_mobile
                and m.member_id = mc.member_id
                order by msg_inbox_id desc
                limit 3 ";

        $stmt = $this->_db->query($sql);
        $res = $stmt->execute(array($memberId));

        return $res;
    }


    public function getCountAlertApproval($memberTypeId, $memberId, $isAgent = "", $memberCode = ""){

        $sql = "select count(1) as num from (
                select count(1) as jml, a.trans_type_id
                from v_transaction a, trans_type b
                where 
                ((a.trans_type_code = 'T003' and trans_status = 'APPROVAL')
                or (a.trans_type_code <> 'T003' and trans_status in ('APPROVAL', 'PAY_CONFIRM')))
                and seq=1 ";

/*
        if ($memberTypeId == 4){
            $sql .= " and (xn3 is null or xn3 = 100) ";
            //$sql .= " and xn3 is null ";
        }else{
            $sql .= " and xn3 = $memberId ";
        }
 *
 */

        if ($memberTypeId == 4){
            //$sql .= " and (v.xn3 is null or v.xn3 = 100) ";
            $sql .= " and account_num2 = 'Q000001' ";
        }else $sql .= " and account_num2 = '$memberCode' "; //$sql .= " and v.xn3 = $memberId  ";

        $sql .= " and b.trans_type_id = a.trans_type_id and b.trans_type_code like 'T%'
                group by a.trans_type_id ";

        if ($isAgent != "" || $memberTypeId == 4){
            $sql .= " union
                select count(1) as jml, trans_type_id 
                from payment_confirm p, v_transaction t
                where payment_status = 0 and t.trans_header_id = p.trans_header_id and seq=1 ";

/*
            if ($memberTypeId == 4){
                $sql .= " and (xn3 is null or xn3 = 100) ";
                //$sql .= " and t.xn3 is null ";
            }else{
                $sql .= " and t.xn3 = $memberId ";
            }
 *
 */
            if ($memberTypeId == 4){
                //$sql .= " and (v.xn3 is null or v.xn3 = 100) ";
                $sql .= " and t.account_num2 = 'Q000001' ";
            }else $sql .= " and t.account_num2 = '$memberCode' "; //$sql .= " and v.xn3 = $memberId  ";
        }

        $sql .= ") x where jml > 0";

        $stmt = $this->_db->query($sql);
        $res = $stmt->execute();
        return $res;

    }


    public function getAlertApproval($memberTypeId, $memberId, $isAgent = "", $memberCode = ""){

        $sql = "select * from (select jml, b.trans_type_code, b.trans_type_name from
                (select count(1) as jml, trans_type_id
                from v_transaction 
                where  ((trans_type_code = 'T003' and trans_status = 'APPROVAL')
                or (trans_type_code <> 'T003' and trans_status in ('APPROVAL', 'PAY_CONFIRM')))
                and seq=1 ";

/*
        if ($memberTypeId == 4){
            $sql .= " and (xn3 is null or xn3 = 100) ";
            //$sql .= " and xn3 = 100 ";
        }else{
            $sql .= " and xn3 = $memberId ";
        }
 * 
 */

        if ($memberTypeId == 4){
            //$sql .= " and (v.xn3 is null or v.xn3 = 100) ";
            $sql .= " and account_num2 = 'Q000001' ";
        }else $sql .= " and account_num2 = '$memberCode' "; //$sql .= " and v.xn3 = $memberId  ";

        $sql .= " group by trans_type_id) a, trans_type b
                where b.trans_type_id = a.trans_type_id and b.trans_type_code like 'T%' ";

        if ($isAgent != "" || $memberTypeId == 4){
            $sql .= " union
                select count(1) as jml, 'CONFIRM', 'Konfirmasi Pembayaran'
                from payment_confirm p, v_transaction t
                where payment_status = 0 and t.trans_header_id = p.trans_header_id
                and t.seq=1 ";

/*
            if ($memberTypeId == 4){
                $sql .= " and (xn3 is null or xn3 = 100) ";
                //$sql .= " and t.xn3 = 100 ";
            }else{
                $sql .= " and t.xn3 = $memberId ";
            }
 * 
 */

            if ($memberTypeId == 4){
                //$sql .= " and (v.xn3 is null or v.xn3 = 100) ";
                $sql .= " and t.account_num2 = 'Q000001' ";
            }else $sql .= " and t.account_num2 = '$memberCode' "; //$sql .= " and v.xn3 = $memberId  ";
        }

        $sql .= ") x where jml > 0";


        //$sql .= " limit 3";
 
        $stmt = $this->_db->query($sql);
        $res = $stmt->execute(array($memberId));

        return $res;

    }


    
    
}
